drop proc dbo.p_FindText
go
create proc dbo.p_FindText(@vcSearchText varchar(1000)) AS

declare @bIsUI bit

if len(@vcSearchText)=36 set @bIsUI=1 else set @bIsUI=0

declare @SQL varchar(8000)

declare @vcTable varchar(255),
	@vcField varchar(255),
	@vcType varchar(255)

declare @vcLastTable varchar(255)
set @vcLastTable=''

declare xcn cursor local forward_only for
select
	a.vcTableName,
	a.vcFieldName,
	a.vcFieldType
from 
	tDDFields a
where
	a.vcServerName='Mercury'
	and a.vcDatabaseName='Evolv_cs'
	and a.vcFieldType in ('char','varchar','uniqueidentifier')
order by
	a.vcTableName,
	a.vcFieldName,
	a.vcFieldType

open xcn

fetch next from xcn into @vcTable,@vcField,@vcType

while @@fetch_status=0
begin
	/*
	if @vcLastTable<>@vcTable 
	begin
		print 'Now scanning table: ' + @vcTable
		set @vcLastTable=@vcTable
	end 

	print '         ' + @vcField +': ' + @vcType
	*/

	if (@vcType='uniqueidentifier' and @bIsUI=1)
		set @SQL='if (select count(*) from evolv_cs.dbo.' + @vcTable + ' where ' + @vcField + ' = ' + char(39) +  @vcSearchText +  char(39) + ')>0 print ' + char(39) + @vcTable + '.' + @vcField + char(39)
	else
		set @SQL='if (select count(*) from evolv_cs.dbo.' + @vcTable + ' where ' + @vcField + ' like ' + char(39) + '%' + @vcSearchText + '%' + char(39) + ')>0 print ' + char(39) + @vcTable + '.' + @vcField + char(39)

	exec(@SQL)

	fetch next from xcn into @vcTable,@vcField,@vcType

end

close xcn

deallocate xcn